-- =============================================
-- This stored procedure takes ContentID and ContactID and returns a row
-- from Content_Authority_Producer
--
-- Modifications
-- 08/12/2003    E.Tatsui   Created
-- =============================================

CREATE     PROCEDURE amsp_CMGetContentSecurity
    @InContentID numeric = NULL,
  @InNavMenuID numeric = NULL,
  @InContactID numeric
AS
BEGIN    
  DECLARE
    @SuperUserFlag bit
  
  SET @SuperUserFlag = 0
  -- First, find out if this user is a member of a super group.
  SELECT @SuperUserFlag = 1
    FROM Content_Authority_Group a, Content_Authority_Producer b
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND a.SuperGroupFlag = 'Y'

  IF @SuperUserFlag = 1
    SELECT b.*
      FROM Content_Authority_Group a, Content_Authority_Producer b
     WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
       AND b.ContactID = @InContactID
       AND a.SuperGroupFlag = 'Y'
  ELSE IF @InContentID IS NOT NULL
    SELECT cap.*
      FROM Content c, Nav_Menu nm, Content_Authority_Producer cap
     WHERE c.NavMenuID = nm.NavMenuID
       AND nm.ContentAuthorityGroupID = cap.ContentAuthorityGroupID
       AND cap.ContactID = @InContactID
       AND c.ContentID = @InContentID
  ELSE IF @InNavMenuID IS NOT NULL
    SELECT cap.*
      FROM Nav_Menu nm, Content_Authority_Producer cap
     WHERE nm.NavMenuID = @InNavMenuID
       AND nm.ContentAuthorityGroupID = cap.ContentAuthorityGroupID
       AND cap.ContactID = @InContactID
  ELSE
    RAISERROR('Either NavMenuID or ContentID is required.',16,1)
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetContentSecurity] TO [IMIS]
GO